package org.mydba.report.gui;

import org.mydba.util.DBAHelper;

import javax.swing.*;
import javax.swing.event.HyperlinkEvent;
import javax.swing.event.HyperlinkListener;
import javax.swing.text.BadLocationException;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.InputEvent;
import java.awt.event.KeyEvent;
import java.io.PrintWriter;
import java.sql.*;
import java.util.*;
import java.util.List;

/**
 * My DBA Report.
 *
 * @since 1.0
 */
// TODO: merge several H2 databases in case of cluster of applications
public class MyDBAReport extends JFrame implements HyperlinkListener {

    private static MyDBAReport report;

    private String startTime;
    private long startTimeNano;
    private String stopTime;
    private long stopTimeNano;

    private String instanceName;
    private String hostName;
    private String version;
    private String startupTime;
    private String archiver;
    private String instanceRole;
    private String dbid;
    private String name;
    private String created;
    private String logMode;
    private String platformName;

    private int numberOfDistinctSessions;
    private List<String> distinctSessions;

    private static List<String> schemasUsed = new ArrayList<String>();
    private static List<ObjectUsed> objectsUsed = new ArrayList<ObjectUsed>();
    private static List<QueryUsed> queriesUsed = new ArrayList<QueryUsed>();

    // graphic components
    static final int FRAME_WIDTH = 1065;
    static final int FRAME_HEIGHT = 760;

    static Color EXECUTING_COLOR = new Color(0, 196, 0);
    static Color FETCHING_COLOR = new Color(128, 255, 255);
    static Color UPDATING_COLOR = new Color(255, 128, 0);

    JTabbedPane tabbedPane;

    JPanel summaryView;
    JPanel objectsUsedView;
    JPanel sqlstatisticsView;
    JPanel sqlExecutionTimeLineView;
    JPanel completeListOfSQLTextView;
    JEditorPane completeListOfSQLTextHtmlPane;
    JPanel completeListOfSQLPlanView;
    JEditorPane completeListOfSQLPlanHtmlPane;
    JPanel completeListOfJavaLocationsView;
    JEditorPane completeListOfJavaLocationsHtmlPane;

    final Map<Integer, JEditorPane> htmlPanes = new HashMap<Integer, JEditorPane>();

    JPanel sessionsView;
    LegendPanel lp;
    SessionsPanel sp;
    MiniMapPanel mmp;

    final Stack<HistoricalPosition> history = new Stack<HistoricalPosition>();

    /**
     * GUI constructor.
     *
     * @param c the connection to the report database
     * @throws SQLException in case of error with the report database
     */
    public MyDBAReport(final Connection c) throws SQLException, BadLocationException {
        super("My DBA Report");

        // initialize from database
        initialize(c);

        // Create and set up the window.
        setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        setBackground(Color.WHITE);
        setResizable(false);

        add(tabbedPane = new JTabbedPane(), BorderLayout.CENTER);
        tabbedPane.setBackground(Color.WHITE);

        tabbedPane.addTab("Summary", createSummaryTab(c));
        tabbedPane.addTab("Objects used", createObjectsUsedTab(c));
        tabbedPane.addTab("SQL statistics", createSQLStatisticsTab(c));
        tabbedPane.addTab("Execution time line", createSQLExecutionTimeLineTab(c));
        tabbedPane.addTab("SQL Queries", createCompleteListOfSQLTextTab(c));
        tabbedPane.addTab("SQL Plans", createCompleteListOfSQLPlanTab(c));
        tabbedPane.addTab("Java Locations", createCompleteListOfJavaLocationsTab(c));
        tabbedPane.addTab("Sessions", createSessionTab(c));

        tabbedPane.setPreferredSize(new Dimension(FRAME_WIDTH + 23, FRAME_HEIGHT));

        // Manage history navigation.
        tabbedPane.getInputMap().put(KeyStroke.getKeyStroke(KeyEvent.VK_LEFT, InputEvent.ALT_DOWN_MASK), "pressed left");

        final Action navigateBack = new AbstractAction() {
            public void actionPerformed(ActionEvent e) {
                // System.out.println("navigate back!");
                try {
                    final HistoricalPosition hp = history.pop();
                    tabbedPane.setSelectedIndex(hp.getTabIndex());
                    final JEditorPane ep = htmlPanes.get(hp.getTabIndex());
                    if (ep != null) {
                        ep.setCaretPosition(hp.getCaretPosition());
                    }
                }
                catch (EmptyStackException ignored) {
                    try {
                        if (tabbedPane.getSelectedIndex() - 1 <= -1) {
                            tabbedPane.setSelectedIndex(tabbedPane.getTabCount() - 1);
                        }
                        else {
                            tabbedPane.setSelectedIndex(tabbedPane.getSelectedIndex() - 1);
                        }
                    }
                    catch (IndexOutOfBoundsException ioobe) {
                        tabbedPane.setSelectedIndex(tabbedPane.getTabCount() - 1);
                    }
                }
            }
        };

        tabbedPane.getActionMap().put("pressed left", navigateBack);

        for (JEditorPane ep : htmlPanes.values()) {
            if (ep != null) {
                ep.getInputMap().put(KeyStroke.getKeyStroke(KeyEvent.VK_LEFT, InputEvent.ALT_DOWN_MASK), "pressed left");
                ep.getInputMap().put(KeyStroke.getKeyStroke(KeyEvent.VK_RIGHT, InputEvent.ALT_DOWN_MASK), "pressed right");
                ep.getActionMap().put("pressed left", navigateBack);
            }
        }

        // Display the window.
        pack();
        setVisible(true);
    }

    public void hyperlinkUpdate(final HyperlinkEvent event) {
        if (event.getEventType() == HyperlinkEvent.EventType.ACTIVATED) {
            String anchor = event.getDescription();
            if (anchor == null) {
                return;
            }

            final JEditorPane htmlPane = htmlPanes.get(tabbedPane.getSelectedIndex());

            history.add(new HistoricalPosition(tabbedPane.getSelectedIndex(), htmlPane != null ? htmlPane.getCaretPosition() : -1));

            anchor = anchor.substring(1);

            if (anchor.startsWith("plan_")) {
                tabbedPane.setSelectedIndex(5);
                completeListOfSQLPlanHtmlPane.scrollToReference(anchor);
            }
            else if (anchor.startsWith("java_loc_")) {
                tabbedPane.setSelectedIndex(6);
                completeListOfJavaLocationsHtmlPane.scrollToReference(anchor);
            }
            else {
                tabbedPane.setSelectedIndex(4);
                completeListOfSQLTextHtmlPane.scrollToReference(anchor);
            }
        }
    }

    private Component createSummaryTab(final Connection c) {
        summaryView = new JPanel(new BorderLayout());

        JEditorPane htmlPane = new JEditorPane("text/html", getSummary());
        htmlPane.setEditable(false);
        htmlPane.setCaretPosition(0);

        htmlPanes.put(tabbedPane.getTabCount(), htmlPane);

        summaryView.add(htmlPane, BorderLayout.CENTER);

        return summaryView;
    }

    private Component createObjectsUsedTab(final Connection c) {
        objectsUsedView = new JPanel(new BorderLayout());

        JEditorPane htmlPane = new JEditorPane("text/html", getObjectsUsed());
        htmlPane.setEditable(false);
        htmlPane.addHyperlinkListener(this);
        htmlPane.setCaretPosition(0);

        htmlPanes.put(tabbedPane.getTabCount(), htmlPane);

        objectsUsedView.add(new JScrollPane(htmlPane), BorderLayout.CENTER);

        return objectsUsedView;
    }

    private Component createSQLStatisticsTab(final Connection c) throws SQLException {
        sqlstatisticsView = new JPanel(new BorderLayout());

        JEditorPane htmlPane = new JEditorPane("text/html", getSQLStatistics(c));
        htmlPane.setEditable(false);
        htmlPane.addHyperlinkListener(this);
        htmlPane.setCaretPosition(0);

        htmlPanes.put(tabbedPane.getTabCount(), htmlPane);

        sqlstatisticsView.add(new JScrollPane(htmlPane), BorderLayout.CENTER);

        return sqlstatisticsView;
    }

    private Component createSQLExecutionTimeLineTab(final Connection c) throws SQLException {
        sqlExecutionTimeLineView = new JPanel(new BorderLayout());

        JEditorPane htmlPane = new JEditorPane("text/html", getSQLExecutionTimeLine(c));
        htmlPane.setEditable(false);
        htmlPane.addHyperlinkListener(this);
        htmlPane.setCaretPosition(0);

        htmlPanes.put(tabbedPane.getTabCount(), htmlPane);

        sqlExecutionTimeLineView.add(new JScrollPane(htmlPane), BorderLayout.CENTER);

        return sqlExecutionTimeLineView;
    }

    private Component createCompleteListOfSQLTextTab(final Connection c) throws SQLException, BadLocationException {
        completeListOfSQLTextView = new JPanel(new BorderLayout());

        completeListOfSQLTextHtmlPane = new JEditorPane("text/html", getCompleteListOfSQLText(c));
        completeListOfSQLTextHtmlPane.setEditable(false);
        completeListOfSQLTextHtmlPane.addHyperlinkListener(this);
        completeListOfSQLTextHtmlPane.setCaretPosition(0);

        htmlPanes.put(tabbedPane.getTabCount(), completeListOfSQLTextHtmlPane);

        completeListOfSQLTextView.add(new JScrollPane(completeListOfSQLTextHtmlPane), BorderLayout.CENTER);

        return completeListOfSQLTextView;
    }

    private Component createCompleteListOfSQLPlanTab(final Connection c) throws SQLException {
        completeListOfSQLPlanView = new JPanel(new BorderLayout());

        completeListOfSQLPlanHtmlPane = new JEditorPane("text/html", getCompleteListOfSQLPlan(c));
        completeListOfSQLPlanHtmlPane.setEditable(false);
        completeListOfSQLPlanHtmlPane.addHyperlinkListener(this);
        completeListOfSQLPlanHtmlPane.setCaretPosition(0);

        htmlPanes.put(tabbedPane.getTabCount(), completeListOfSQLPlanHtmlPane);

        completeListOfSQLPlanView.add(new JScrollPane(completeListOfSQLPlanHtmlPane), BorderLayout.CENTER);

        return completeListOfSQLPlanView;
    }

    private Component createCompleteListOfJavaLocationsTab(final Connection c) throws SQLException {
        completeListOfJavaLocationsView = new JPanel(new BorderLayout());

        completeListOfJavaLocationsHtmlPane = new JEditorPane("text/html", getCompleteListOfJavaLocations(c));
        completeListOfJavaLocationsHtmlPane.setEditable(false);
        completeListOfJavaLocationsHtmlPane.addHyperlinkListener(this);
        completeListOfJavaLocationsHtmlPane.setCaretPosition(0);

        htmlPanes.put(tabbedPane.getTabCount(), completeListOfJavaLocationsHtmlPane);

        completeListOfJavaLocationsView.add(new JScrollPane(completeListOfJavaLocationsHtmlPane), BorderLayout.CENTER);

        return completeListOfJavaLocationsView;
    }

    private JPanel createSessionTab(final Connection c) {
        sessionsView = new JPanel(new GridBagLayout());

        final GridBagConstraints gbc = new GridBagConstraints();

        // Add the text field to the applet.
        gbc.gridx = 0;
        gbc.gridy = 0;
        gbc.gridwidth = 1;
        gbc.gridheight = 1;
        gbc.weightx = 0;
        gbc.weighty = 0;
        gbc.anchor = GridBagConstraints.PAGE_START;
        //gbc.fill = GridBagConstraints.HORIZONTAL;
        sessionsView.add(lp = new LegendPanel(this), gbc);

        gbc.gridy = 1;
        gbc.weightx = 1;
        gbc.weighty = 1;
        gbc.anchor = GridBagConstraints.CENTER;
        gbc.fill = GridBagConstraints.BOTH;
        final JScrollPane scrollPane = new JScrollPane(sp = new SessionsPanel(this, c),
                                                       JScrollPane.VERTICAL_SCROLLBAR_AS_NEEDED,
                                                       JScrollPane.HORIZONTAL_SCROLLBAR_NEVER);
        scrollPane.setPreferredSize(sp.getPreferredSize());
        sessionsView.add(scrollPane, gbc);

        gbc.gridy = 2;
        gbc.weightx = 0;
        gbc.weighty = 0;
        gbc.anchor = GridBagConstraints.PAGE_END;
        //gbc.fill = GridBagConstraints.HORIZONTAL;
        sessionsView.add(mmp = new MiniMapPanel(this, c, sp), gbc);

        sessionsView.setPreferredSize(new Dimension(FRAME_WIDTH, lp.getPreferredSize().height +
                                                                 sp.getPreferredSize().height +
                                                                 mmp.getPreferredSize().height));

        htmlPanes.put(tabbedPane.getTabCount(), null);

        return sessionsView;
    }

    private static void createAndShowGUI(final Connection c) throws SQLException, BadLocationException {
        // instanciate the object for reporting
        report = new MyDBAReport(c);
    }

    public static void main(final String[] args) throws Throwable {

        System.out.println("MyDBA report starting...");

        try {
            final Connection c = getReportDbConnection();

            SwingUtilities.invokeLater(new Runnable() {
                public void run() {
                    try {
                        createAndShowGUI(c);
                    }
                    catch (Throwable t) {
                        System.err.println("GUI creation didn't successfully complete!");
                        t.printStackTrace();
                    }
                }
            });

//            printCompleteListOfJavaLocation(out, c);

            Runtime.getRuntime().addShutdownHook(
                    new Thread() {
                        public void run() {
                            DBAHelper.close(c);
                        }
                    });

        }
        catch (Throwable t) {
            t.printStackTrace();
        }
    }

    /**
     * Generate HTML header (CSS, title...).
     */
    private static String getHTMLHeader() {
        return "<html><head><title>My DBA report</title><style type=\"text/css\">" +
               "body    {font:bold 14pt Arial,Helvetica,Geneva,sans-serif;color:black; background:White;}\n" +
               "h1      {font:bold 22pt Arial,Helvetica,Geneva,sans-serif;color:#336699;background-color:White;border-bottom:1px solid #cccc99;margin-top:0pt; margin-bottom:0pt;padding:0px 0px 0px 0px;}\n" +
               "h2      {font:bold 20pt Arial,Helvetica,Geneva,sans-serif;color:#336699;background-color:White;margin-top:4pt; margin-bottom:0pt;}\n" +
               "h3      {font:bold 18pt Arial,Helvetica,Geneva,sans-serif;color:#336699;background-color:White;margin-top:4pt; margin-bottom:0pt;}" +
               "li      {font: 11pt Arial,Helvetica,Geneva,sans-serif; color:black; background:White;}\n" +
               "th.nobg {font:bold 10pt Arial,Helvetica,Geneva,sans-serif; color:black; background:White;padding-left:4px; padding-right:4px;padding-bottom:2px}" +
               "th.bg   {font:bold 10pt Arial,Helvetica,Geneva,sans-serif; color:White; background:#0066CC;padding-left:4px; padding-right:4px;padding-bottom:2px}\n" +
               "td.nc   {font:10pt Arial,Helvetica,Geneva,sans-serif;color:black;background:White;vertical-align:top;}\n" +
               "td.c    {font:10pt Arial,Helvetica,Geneva,sans-serif;color:black;background:#FFFFCC; vertical-align:top;}" +
               "td.rnc  {font:10pt Arial,Helvetica,Geneva,sans-serif;color:black;background:White;vertical-align:top;text-align:right;}\n" +
               "td.rc   {font:10pt Arial,Helvetica,Geneva,sans-serif;color:black;background:#FFFFCC; vertical-align:top;text-align:right;}" +
               "a       {font:bold 10pt Arial,Helvetica,sans-serif;color:#663300; vertical-align:top;margin-top:0pt; margin-bottom:0pt;}\n" +
               "</style></head><body>";
    }

    /**
     * Generate HTML footer.
     */
    private static String getHTMLFooter() {
        return "</body></html>";
    }

    /**
     * Initialize the report.
     *
     * @param c a connection to the local report database
     * @throws SQLException in case of problem
     */
    public void initialize(final Connection c) throws SQLException {
        System.out.println("Building index to speed up reporting...");
        buildReportingIndexes(c);
        System.out.println("Initializing from report database...");
        readTimeInfo(c);
        readDatabaseInfo(c);
        readInstanceInfo(c);
        readNumberOfDistinctSessions(c);
        readDistinctSessions(c);
        // get usage of objects (schemas, index+tables, queries)
        readSchemasUsed(c);
        readObjectsUsed(c);
        readQueriesUsed(c);
    }

    private void buildReportingIndexes(Connection c) throws SQLException {
        Statement s = c.createStatement();
        s.execute("create index if not exists idx_sqlexec_1 on sqlexec (stime)");
        s.execute("create index if not exists idx_sqlexec_2 on sqlexec (etime)");
        s.execute("create index if not exists idx_sqlexec_3 on sqlexec (rset_next_start_time_ns)");
        //s.execute("create index if not exists idx_sqlexec_4 on sqlexec (rset_next_end_time_ns)");
        s.execute("create index if not exists idx_sqlexec_5 on sqlexec (rows_update_start_time_ns)");
        //s.execute("create index if not exists idx_sqlexec_6 on sqlexec (rows_update_end_time_ns)");
        DBAHelper.close(s);
    }

    /**
     * Read time information from database.
     *
     * @param c a connection to the local report database
     * @throws SQLException in case of problem
     */
    private void readTimeInfo(Connection c) throws SQLException {
        Statement s = c.createStatement();
        ResultSet r = s.executeQuery("select formatdatetime(start_time,'dd-MMM-yyyy HH:mm:ss'), start_time_ns, formatdatetime(end_time,'dd-MMM-yyyy HH:mm:ss'), end_time_ns from mydba_run");
        if (r.next()) {
            this.startTime = r.getString(1);
            this.startTimeNano = r.getLong(2);
            this.stopTime = r.getString(3);
            this.stopTimeNano = r.getLong(4);
        }
        DBAHelper.close(r);
        DBAHelper.close(s);
    }

    public String getStartTime() {
        return startTime;
    }

    public long getStartTimeNano() {
        return startTimeNano;
    }

    public String getStopTime() {
        return stopTime;
    }

    public long getStopTimeNano() {
        return stopTimeNano;
    }

    private void readDatabaseInfo(Connection c) throws SQLException {
        Statement s = c.createStatement();
        ResultSet r = s.executeQuery("SELECT dbid, NAME, formatdatetime(created,'dd-MMM-yyyy HH:mm:ss'), log_mode, platform_name FROM mydba_database");
        if (r.next()) {
            this.dbid = r.getString(1);
            this.name = r.getString(2);
            this.created = r.getString(3);
            this.logMode = r.getString(4);
            this.platformName = r.getString(5);
        }
        DBAHelper.close(r);
        DBAHelper.close(s);
    }

    private void readInstanceInfo(Connection c) throws SQLException {
        Statement s = c.createStatement();
        ResultSet r = s.executeQuery("SELECT instance_name, host_name, version, formatdatetime(startup_time,'dd-MMM-yyyy HH:mm:ss'), archiver, instance_role FROM mydba_instance");
        if (r.next()) {
            this.instanceName = r.getString(1);
            this.hostName = r.getString(2);
            this.version = r.getString(3);
            this.startupTime = r.getString(4);
            this.archiver = r.getString(5);
            this.instanceRole = r.getString(5);
        }
        DBAHelper.close(r);
        DBAHelper.close(s);
    }

    public String getInstanceName() {
        return instanceName;
    }

    public String getHostName() {
        return hostName;
    }

    public String getVersion() {
        return version;
    }

    public String getStartupTime() {
        return startupTime;
    }

    public String getArchiver() {
        return archiver;
    }

    public String getInstanceRole() {
        return instanceRole;
    }

    public String getDbid() {
        return dbid;
    }

    public String getName() {
        return name;
    }

    public String getCreated() {
        return created;
    }

    public String getLogMode() {
        return logMode;
    }

    public String getPlatformName() {
        return platformName;
    }

    private void readNumberOfDistinctSessions(Connection c) throws SQLException {
        Statement s = c.createStatement();
        ResultSet r = s.executeQuery("SELECT count(distinct sid) from sqlexec");
        if (r.next()) {
            this.numberOfDistinctSessions = r.getInt(1);
        }
        DBAHelper.close(r);
        DBAHelper.close(s);
    }

    private void readDistinctSessions(Connection c) throws SQLException {
        Statement s = c.createStatement();
        ResultSet r = s.executeQuery("SELECT distinct sid from sqlexec order by 1");
        this.distinctSessions = new ArrayList<String>();
        while (r.next()) {
            this.distinctSessions.add(r.getString(1));
        }
        DBAHelper.close(r);
        DBAHelper.close(s);
    }

    public int getNumberOfDistinctSessions() {
        return numberOfDistinctSessions;
    }

    public List<String> getDistinctSessions() {
        return distinctSessions;
    }

    private static void printSQLExecutionTimeline(PrintWriter out, Connection c) throws SQLException {
        Statement s = c.createStatement();
        ResultSet r = s.executeQuery("select " +
                                     "e.id, " +
                                     "e.sid, " +
                                     "e.sql_id, " +
                                     "e.child_number, " +
                                     "formatdatetime(dateadd('MILLISECOND',round((e.stime-r.start_time_ns)/1000000.0,3),r.start_time),'dd-MMM-yyyy HH:mm:ss.SSS'), " +
                                     "formatdatetime(dateadd('MILLISECOND',round((e.etime-r.start_time_ns)/1000000.0,3),r.start_time),'dd-MMM-yyyy HH:mm:ss.SSS'), " +
                                     "e.rset_number_of_next, " +
                                     "round(e.rset_next_duration/1000000.0,3), " +
                                     "e.rows_updated, " +
                                     "round(e.rows_update_duration/1000000.0,3), " +
                                     "round((e.etime-e.stime)/1000000.0,3) " +
                                     "from " +
                                     "sqlexec e, mydba_run r " +
                                     "order by e.id");

        out.println("<P>\n" +
                    "<H3>SQL Execution Timeline</H3>" +
                    "</P>" +
                    "<P>\n" +
                    "<TABLE BORDER=1 WIDTH=100%>\n" +
                    "<TR>" +
                    "<TH class='bg'>Order</TH>" +
                    "<TH class='bg'>SID</TH>" +
                    "<TH class='bg'>SQL Id:Child number<br>Java location</TH>" +
                    "<TH class='bg' width=130>Start time<br>End time</TH>" +
                    "<TH class='bg'>Execution Duration (ms)</TH>" +
                    "<TH class='bg'>Rows processed<br>Duration (ms)</TH>" +
                    "<TH class='bg'>Consistent gets<br>Physical reads</TH>" +
                    "<TH class='bg'>Bytes sent to/rec. from client<br>SQL*Net roundtrips</TH>" +
                    "<TH class='bg'>sorts memory<br>sorts disk</TH>" +
                    "</TR>");

        int i = 0;
        while (r.next()) {
            out.print("<TR>");
            out.print("<TD class='" + (i % 2 == 0 ? "rnc" : "rc") + "'>" + r.getString(1) + "</TD>");
            out.print("<TD class='" + (i % 2 == 0 ? "rnc" : "rc") + "'>" + r.getString(2) + "</TD>");
            out.print("<TD class='" + (i % 2 == 0 ? "rnc" : "rc") + "'>" +
                      "<A HREF='#" + r.getString(3) + "'>" + r.getString(3) + "</A>:<A HREF='#plan_" + r.getString(3) + "_" + r.getString(4) + "'>" + r.getString(4) + "</A>" +
                      "<br><TT><A HREF='#java_loc_" + r.getString(1) + "'>" + getShortJavaLocation(c, r.getLong(1)) + "</A></TT></TD>");
            out.print("<TD class='" + (i % 2 == 0 ? "rnc" : "rc") + "'>" + r.getString(5) + "<br>" + r.getString(6) + "</TD>");
            out.print("<TD class='" + (i % 2 == 0 ? "rnc" : "rc") + "'>" + r.getString(11) + "</TD>");

            //System.out.println(r.getString(3)+": "+r.getLong(7)+", "+r.getString(8)+", "+r.getLong(9));

            long v = r.getLong(9);
            boolean notFetched = false;
            if (r.wasNull() || v == -1) {
                v = r.getLong(7);
                if (v == -1) {
                    if (r.getString(8) == null) {
                        notFetched = true;
                    }

                    v = 0;
                }
            }

            if (notFetched) {
                out.print("<TD class='" + (i % 2 == 0 ? "rnc" : "rc") + "'><b>No fetch!</b></TD>");
            }
            else {
                if (r.getString(8) == null) {
                    // update
                    out.print("<TD class='" + (i % 2 == 0 ? "rnc" : "rc") + "'>" + v + "<br>" + (r.getString(10) == null ? "N/A" : r.getString(10)) + "</TD>");
                }
                else {
                    out.print("<TD class='" + (i % 2 == 0 ? "rnc" : "rc") + "'>" + v + "<br>" + r.getString(8) + "</TD>");
                }
            }

            // statistics
            final ExecutionStatistics es = getExecutionStatistics(c, r.getLong(1));
            out.print("<TD class='" + (i % 2 == 0 ? "rnc" : "rc") + "'>" + es.cg + "<br>" + es.pr + "</TD>");
            out.print("<TD class='" + (i % 2 == 0 ? "rnc" : "rc") + "'>" + es.bstc + " / " + es.brfc + "<br>" + es.rttfc + "</TD>");
            out.print("<TD class='" + (i % 2 == 0 ? "rnc" : "rc") + "'>" + es.sortsmem + "<br>" + es.sortsdisk + "</TD>");


            out.println("</TR>");

            i++;
        }

        out.println("</TABLE>\n</P>");

        DBAHelper.close(r);
        DBAHelper.close(s);
    }

    private static ExecutionStatistics getExecutionStatistics(Connection c, long id) throws SQLException {
        PreparedStatement p = null;
        ResultSet r = null;
        ExecutionStatistics es = new ExecutionStatistics();
        try {
            p = c.prepareStatement("select osn.name, ss.value " +
                                   "from sqlexec_statistics ss, oracle_statistics_name osn " +
                                   "where ss.sqlexec_id=? and ss.statistic_id=osn.id " +
                                   "and osn.name in ('SQL*Net roundtrips to/from client','bytes received via SQL*Net from client','bytes sent via SQL*Net to client'," +
                                   "'sorts (memory)','sorts (disk)','physical reads','consistent gets')" +
                                   "order by osn.name");

            p.setLong(1, id);
            r = p.executeQuery();
            while (r.next()) {
                if ("SQL*Net roundtrips to/from client".equals(r.getString(1))) {
                    es.rttfc = r.getString(2);
                }
                else if ("physical reads".equals(r.getString(1))) {
                    es.pr = r.getString(2);
                }
                else if ("consistent gets".equals(r.getString(1))) {
                    es.cg = r.getString(2);
                }
                else if ("sorts (memory)".equals(r.getString(1))) {
                    es.sortsmem = r.getString(2);
                }
                else if ("sorts (disk)".equals(r.getString(1))) {
                    es.sortsdisk = r.getString(2);
                }
                else if ("bytes received via SQL*Net from client".equals(r.getString(1))) {
                    es.brfc = r.getString(2);
                }
                else if ("bytes sent via SQL*Net to client".equals(r.getString(1))) {
                    es.bstc = r.getString(2);
                }
            }
        }
        finally {

            DBAHelper.close(r);
            DBAHelper.close(p);
        }

        return es;
    }

    private static String getShortJavaLocation(Connection c, long id) throws SQLException {
        PreparedStatement p = null;
        ResultSet r = null;
        try {
            p = c.prepareStatement("select top 1 class, method, line from sqlexec_origine where sqlexec_id=? order by id");
            p.setLong(1, id);
            r = p.executeQuery();
            if (r.next()) {
                String location = r.getString(1) + "." + r.getString(2) + "(" + r.getLong(3) + ")";
                if (location.length() <= 30) {
                    return location;
                }
                else {
                    return location.substring(location.length() - 30, location.length());
                }
            }
            else {
                return null;
            }
        }
        finally {

            DBAHelper.close(r);
            DBAHelper.close(p);
        }
    }

    /**
     * Generate report summary with general information.
     */
    private String getSummary() {
        final StringBuilder sb = new StringBuilder(getHTMLHeader());
        sb.append("<h1>My DBA report</h1>");

        sb.append("<P>\n" +
                  "<TABLE BORDER=1 WIDTH=1024>\n" +
                  "<TR><TH class='bg'>DB Name</TH>" +
                  "<TH class='bg'>DB Id</TH>" +
                  "<TH class='bg'>Instance</TH>" +
                  "<TH class='bg'>Log mode</TH>" +
                  "<TH class='bg'>Release</TH>" +
                  "<TH class='bg'>Host</TH>" +
                  "<TH class='bg'>Platform</TH></TR>\n" +
                  "<TR><TD class='nc'>" + getName() + "</TD>" +
                  "<TD ALIGN='right' class='nc'>" + getDbid() + "</TD>" +
                  "<TD class='nc'>" + getInstanceName() + "</TD>" +
                  "<TD class='nc'>" + getLogMode() + "</TD>" +
                  "<TD class='nc'>" + getVersion() + "</TD>" +
                  "<TD class='nc'>" + getHostName() + "</TD>" +
                  "<TD class='nc'>" + getPlatformName() + "</TD></TR>\n" +
                  "</TABLE>" +
                  "</P>");

        sb.append("<P>\n" +
                  "<TABLE BORDER=1 WIDTH=1024>\n" +
                  "<TR><TH class='bg'>Start time</TH>" +
                  "<TH class='bg'>End time</TH>" +
                  "<TH class='bg'>Duration</TH>" +
                  "<TH class='bg'>Distinct connections</TH></TR>\n" +
                  "<TR><TD ALIGN='right' class='nc'>" + getStartTime() + "</TD>" +
                  "<TD ALIGN='right' class='nc'>" + getStopTime() + "</TD>" +
                  "<TD ALIGN='right' class='nc'>" + formatTimeElapsed(Math.round(((double) (getStopTimeNano() - getStartTimeNano())) / 1000000.0), true) + "</TD>" +
                  "<TD ALIGN='right' class='nc'>" + getNumberOfDistinctSessions() + "</TD></TR>\n" +
                  "</TABLE>" +
                  "</P>");

        sb.append(getHTMLFooter());

        return sb.toString();
    }

    /**
     * Generate report objects usage.
     */
    private String getObjectsUsed() {
        final StringBuilder sb = new StringBuilder(getHTMLHeader());
        sb.append("<h1>Schemas, objects and queries used</h1>");

        sb.append("<P>\n" +
                  "Schemas" +
                  "</P>\n" +
                  "<P>\n" +
                  "<TABLE BORDER=1 WIDTH=1024>\n" +
                  "<TR><TH class='bg'>Schema</TH></TR>\n");

        int i = 0;
        for (String schema : schemasUsed) {
            sb.append("<TR><TD ALIGN='right' class='" + (i % 2 == 0 ? "nc" : "c") + "'>" + schema + "</TD></TR>");
            i++;
        }

        sb.append("</TABLE>\n</P>");

        sb.append("<P>\n" +
                  "Objects" +
                  "</P>\n" +
                  "<P>\n" +
                  "<TABLE BORDER=1 WIDTH=1024>\n" +
                  "<TR><TH class='bg'>Owner</TH><TH class='bg'>Name</TH><TH class='bg'>Type</TH></TR>\n");

        i = 0;
        for (ObjectUsed o : objectsUsed) {
            sb.append("<TR>");
            sb.append("<TD ALIGN='right' class='" + (i % 2 == 0 ? "nc" : "c") + "'>" + o.owner + "</TD>");
            sb.append("<TD ALIGN='right' class='" + (i % 2 == 0 ? "nc" : "c") + "'>" + o.name + "</TD>");
            sb.append("<TD ALIGN='right' class='" + (i % 2 == 0 ? "nc" : "c") + "'>" + (o.type == null ? "&nbsp;" : o.type) + "</TD>");
            sb.append("</TR>");
            i++;
        }

        sb.append("</TABLE>\n</P>");

        sb.append("<P>\n" +
                  "Queries" +
                  "</P>\n" +
                  "<P>\n" +
                  "<TABLE BORDER=1 WIDTH=1024>\n" +
                  "<TR><TH class='bg'>SQL Id</TH><TH class='bg'>Versions</TH><TH class='bg'>Type</TH><TH class='bg'>Executions</TH></TR>\n");

        i = 0;
        for (QueryUsed q : queriesUsed) {
            sb.append("<TR>");
            sb.append("<TD ALIGN='right' class='" + (i % 2 == 0 ? "nc" : "c") + "'><A HREF='#" + q.sqlId + "'>" + q.sqlId + "</A></TD>");
            sb.append("<TD ALIGN='right' class='" + (i % 2 == 0 ? "nc" : "c") + "'>" + q.childNumber + "</TD>");
            sb.append("<TD ALIGN='right' class='" + (i % 2 == 0 ? "nc" : "c") + "'>" + q.commandType + "</TD>");
            sb.append("<TD ALIGN='right' class='" + (i % 2 == 0 ? "nc" : "c") + "'>" + q.executions + "</TD>");
            sb.append("</TR>");
            i++;
        }

        sb.append("</TABLE>\n</P>");

        sb.append(getHTMLFooter());

        return sb.toString();
    }

    /**
     * Generate report SQL statistics.
     */
    private String getSQLStatistics(Connection c) throws SQLException {
        final StringBuilder sb = new StringBuilder(getHTMLHeader());
        sb.append("<h1>SQL Statistics</h1>");

        Statement s = c.createStatement();
        ResultSet r = s.executeQuery("select TOP 50 e.*, substring(t.sql_text,1,27) " +
                                     "from (SELECT " +
                                     "e.sql_id, " +
                                     "round(min(e.etime-e.stime)/1000000000.0,3), " +
                                     "round(max(e.etime-e.stime)/1000000000.0,3), " +
                                     "round(sum(e.etime-e.stime)/1000000000.0,3) as total_duration, " +
                                     "count(*), " +
                                     "round(avg(e.etime-e.stime)/1000000000.0,3) " +
                                     "from sqlexec e group by sql_id) e, sqltext t where t.sql_id = e.sql_id " +
                                     "order by e.total_duration desc");

        sb.append(
                "<P><H2 class='awr'>SQL ordered by Elapsed Time</H2></P>\n" +
                "<TABLE BORDER=1 WIDTH=1024>\n" +
                "<TR><TH class='bg'>Total Elapsed Time (s)</TH>" +
                "<TH class='bg'>Executions</TH>" +
                "<TH class='bg'>Elapsed per Exec (s)</TH>" +
                "<TH class='bg'>Minimal Elapsed per Exec (s)</TH>" +
                "<TH class='bg'>Maximal Elapsed per Exec (s)</TH>" +
                "<TH class='bg'>SQL Id</TH>" +
                "<TH class='bg'>SQL Text</TH>" +
                "</TR>");

        int i = 0;
        while (r.next()) {
            sb.append("<TR>");
            sb.append("<TD class='" + (i % 2 == 0 ? "rnc" : "rc") + "'>" + r.getLong(4) + "</TD>");
            sb.append("<TD class='" + (i % 2 == 0 ? "rnc" : "rc") + "'>" + r.getLong(5) + "</TD>");
            sb.append("<TD class='" + (i % 2 == 0 ? "rnc" : "rc") + "'>" + r.getLong(6) + "</TD>");
            sb.append("<TD class='" + (i % 2 == 0 ? "rnc" : "rc") + "'>" + r.getLong(2) + "</TD>");
            sb.append("<TD class='" + (i % 2 == 0 ? "rnc" : "rc") + "'>" + r.getLong(3) + "</TD>");
            sb.append("<TD class='" + (i % 2 == 0 ? "rnc" : "rc") + "'><A HREF='#" + r.getString(1) + "'>" + r.getString(1) + "</A></TD>");
            sb.append("<TD class='" + (i % 2 == 0 ? "nc" : "c") + "'>" + r.getString(7).replaceAll(" ", "&nbsp;").replaceAll("\n", "&nbsp;") + "...</TD>");
            sb.append("</TR>");

            i++;
        }

        sb.append("</TABLE>\n</P>");

        DBAHelper.close(r);
        DBAHelper.close(s);

        sb.append(getHTMLFooter());

        return sb.toString();
    }

    /**
     * Generate report SQL execution time line.
     */
    private String getSQLExecutionTimeLine(Connection c) throws SQLException {
        final StringBuilder sb = new StringBuilder(getHTMLHeader());
        sb.append("<h1>SQL Execution Time Line</h1>");

        Statement s = c.createStatement();
        ResultSet r = s.executeQuery("select " +
                                     "e.id, " +
                                     "e.sid, " +
                                     "e.sql_id, " +
                                     "e.child_number, " +
                                     "formatdatetime(dateadd('MILLISECOND',round((e.stime-r.start_time_ns)/1000000.0,3),r.start_time),'dd-MMM-yyyy HH:mm:ss.SSS'), " +
                                     "formatdatetime(dateadd('MILLISECOND',round((e.etime-r.start_time_ns)/1000000.0,3),r.start_time),'dd-MMM-yyyy HH:mm:ss.SSS'), " +
                                     "e.rset_number_of_next, " +
                                     "round(e.rset_next_duration/1000000.0,3), " +
                                     "e.rows_updated, " +
                                     "round(e.rows_update_duration/1000000.0,3), " +
                                     "round((e.etime-e.stime)/1000000.0,3) " +
                                     "from " +
                                     "sqlexec e, mydba_run r " +
                                     "order by e.id");

        sb.append("<P>\n" +
                  "<H3>SQL Execution Timeline</H3>" +
                  "</P>" +
                  "<P>\n" +
                  "<TABLE BORDER=1 WIDTH=100%>\n" +
                  "<TR>" +
                  "<TH class='bg'>Order</TH>" +
                  "<TH class='bg'>SID</TH>" +
                  "<TH class='bg'>SQL Id:Child number<br>Java location</TH>" +
                  "<TH class='bg' width=130>Start time<br>End time</TH>" +
                  "<TH class='bg'>Execution Duration (ms)</TH>" +
                  "<TH class='bg'>Rows processed<br>Duration (ms)</TH>" +
                  "<TH class='bg'>Consistent gets<br>Physical reads</TH>" +
                  "<TH class='bg'>Bytes sent to/rec. from client<br>SQL*Net roundtrips</TH>" +
                  "<TH class='bg'>sorts memory<br>sorts disk</TH>" +
                  "</TR>");

        int i = 0;
        while (r.next()) {
            sb.append("<TR>");
            sb.append("<TD class='" + (i % 2 == 0 ? "rnc" : "rc") + "'>" + r.getString(1) + "</TD>");
            sb.append("<TD class='" + (i % 2 == 0 ? "rnc" : "rc") + "'>" + r.getString(2) + "</TD>");
            sb.append("<TD class='" + (i % 2 == 0 ? "rnc" : "rc") + "'>" +
                      "<A HREF='#" + r.getString(3) + "'>" + r.getString(3) + "</A>:<A HREF='#plan_" + r.getString(3) + "_" + r.getString(4) + "'>" + r.getString(4) + "</A>" +
                      "<br><TT><A HREF='#java_loc_" + r.getString(1) + "'>" + getShortJavaLocation(c, r.getLong(1)) + "</A></TT></TD>");
            sb.append("<TD class='" + (i % 2 == 0 ? "rnc" : "rc") + "'>" + r.getString(5) + "<br>" + r.getString(6) + "</TD>");
            sb.append("<TD class='" + (i % 2 == 0 ? "rnc" : "rc") + "'>" + r.getString(11) + "</TD>");

            //System.out.println(r.getString(3)+": "+r.getLong(7)+", "+r.getString(8)+", "+r.getLong(9));

            long v = r.getLong(9);
            boolean notFetched = false;
            if (r.wasNull() || v == -1) {
                v = r.getLong(7);
                if (v == -1) {
                    if (r.getString(8) == null) {
                        notFetched = true;
                    }

                    v = 0;
                }
            }

            if (notFetched) {
                sb.append("<TD class='" + (i % 2 == 0 ? "rnc" : "rc") + "'><b>No fetch!</b></TD>");
            }
            else {
                if (r.getString(8) == null) {
                    // update
                    sb.append("<TD class='" + (i % 2 == 0 ? "rnc" : "rc") + "'>" + v + "<br>" + (r.getString(10) == null ? "N/A" : r.getString(10)) + "</TD>");
                }
                else {
                    sb.append("<TD class='" + (i % 2 == 0 ? "rnc" : "rc") + "'>" + v + "<br>" + r.getString(8) + "</TD>");
                }
            }

            // statistics
            final ExecutionStatistics es = getExecutionStatistics(c, r.getLong(1));
            sb.append("<TD class='" + (i % 2 == 0 ? "rnc" : "rc") + "'>" + es.cg + "<br>" + es.pr + "</TD>");
            sb.append("<TD class='" + (i % 2 == 0 ? "rnc" : "rc") + "'>" + es.bstc + " / " + es.brfc + "<br>" + es.rttfc + "</TD>");
            sb.append("<TD class='" + (i % 2 == 0 ? "rnc" : "rc") + "'>" + es.sortsmem + "<br>" + es.sortsdisk + "</TD>");


            sb.append("</TR>");

            i++;
        }

        sb.append("</TABLE>\n</P>");

        DBAHelper.close(r);
        DBAHelper.close(s);

        sb.append(getHTMLFooter());

        return sb.toString();
    }

    /**
     * Generate report SQL query list.
     */
    private String getCompleteListOfSQLText(Connection c) throws SQLException {
        final StringBuilder sb = new StringBuilder(getHTMLHeader());
        sb.append("<h1>Complete List of SQL Text</h1>");

        Statement s = c.createStatement();
        ResultSet r = s.executeQuery("SELECT t.sql_id, t.sql_text, " +
                                     "(select count(*) from sqlplan p where p.sql_id=t.sql_id), " +
                                     "(select min(child_number) from sqlplan p where p.sql_id=t.sql_id) from sqltext t order by t.sql_id");

        sb.append("<P>\n" +
                  "<TABLE BORDER=1 WIDTH=1024>\n" +
                  "<TR><TH class='bg'>SQL Id</TH><TH class='bg'>SQL Text</TH><TH class='bg'>Number of plans</TH></TR>");

        int i = 0;
        while (r.next()) {
            sb.append("<TR>");
            sb.append("<TD class='" + (i % 2 == 0 ? "rnc" : "rc") + "'><A NAME='" + r.getString(1) + "'></A>" + r.getString(1) + "</TD>");
            sb.append("<TD class='" + (i % 2 == 0 ? "nc" : "c") + "'>" + r.getString(2).replaceAll(" ", "&nbsp;").replaceAll("\n", "<br>\n") + "</TD>");
            sb.append("<TD class='" + (i % 2 == 0 ? "rnc" : "rc") + "'><A HREF='#plan_" + r.getString(1) + "_" + r.getString(4) + "'>" + r.getInt(3) + "</A></TD>");
            sb.append("</TR>");

            i++;
        }

        sb.append("</TABLE>\n</P>");

        DBAHelper.close(r);
        DBAHelper.close(s);

        sb.append(getHTMLFooter());

        return sb.toString();
    }

    /**
     * Generate report SQL execution plan list.
     */
    private String getCompleteListOfSQLPlan(Connection c) throws SQLException {
        final StringBuilder sb = new StringBuilder(getHTMLHeader());
        sb.append("<h1>Complete List of SQL Execution Plan</h1>");

        Statement s = c.createStatement();
        ResultSet r = s.executeQuery("SELECT sql_id, child_number, plan_text from sqlplan order by sql_id, child_number");

        sb.append("<P>\n" +
                  "<TABLE BORDER=1 WIDTH=1024>\n" +
                  "<TR><TH class='bg'>SQL Id</TH><TH class='bg'>Child number</TH><TH class='bg'>Execution plan</TH></TR>");

        int i = 0;
        while (r.next()) {
            sb.append("<TR>");
            sb.append("<TD class='" + (i % 2 == 0 ? "rnc" : "rc") + "'><A NAME='plan_" + r.getString(1) + "_" + r.getString(2) + "'></A>" + r.getString(1) + "</TD>");
            sb.append("<TD class='" + (i % 2 == 0 ? "rnc" : "rc") + "'>" + r.getString(2) + "</TD>");
            sb.append("<TD class='" + (i % 2 == 0 ? "nc" : "c") + "'><tt>" + r.getString(3).replaceAll(" ", "&nbsp;").replaceAll("\n", "<br>\n") + "</tt></TD>");
            sb.append("</TR>");

            i++;
        }

        sb.append("</TABLE>\n</P>");

        DBAHelper.close(r);
        DBAHelper.close(s);

        sb.append(getHTMLFooter());

        return sb.toString();
    }

    /**
     * Generate report Java locations.
     */
    private String getCompleteListOfJavaLocations(Connection c) throws SQLException {
        final StringBuilder sb = new StringBuilder(getHTMLHeader());
        sb.append("<h1>Complete List of Java Locations</h1>");

        Statement s = c.createStatement();
        ResultSet r = s.executeQuery("SELECT sqlexec_id, class, method, line from sqlexec_origine order by sqlexec_id, id");

        sb.append("<P>\n" +
                  "<TABLE BORDER=1 WIDTH=1024>\n" +
                  "<TR><TH class='bg'>Execution Id</TH>" +
                  "<TH class='bg'>Java location</TH></TR>");

        int i = 0;
        String oldId = null;
        final StringBuilder trace = new StringBuilder();
        String currentId;
        while (r.next()) {
            currentId = r.getString(1);
            if (oldId != null && !currentId.equals(oldId)) {
                sb.append("<TR>");
                sb.append("<TD class='" + (i % 2 == 0 ? "rnc" : "rc") + "'><A NAME='java_loc_" + oldId + "'></A>" + oldId + "</TD>");
                sb.append("<TD class='" + (i % 2 == 0 ? "nc" : "c") + "'><TT>" + trace.toString() + "</TT></TD>");
                sb.append("</TR>");

                i++;
                trace.setLength(0);
            }

            trace.append("at ").append(r.getString(2)).append('.').append(r.getString(3)).append('(').append(r.getString(4)).append(")<br>");

            oldId = currentId;
        }

        if (oldId != null) {
            sb.append("<TR>");
            sb.append("<TD class='" + (i % 2 == 0 ? "rnc" : "rc") + "'><A NAME='java_loc_" + oldId + "'></A>" + oldId + "</TD>");
            sb.append("<TD class='" + (i % 2 == 0 ? "nc" : "c") + "'><TT>" + trace.toString() + "</TT></TD>");
            sb.append("</TR>");
        }

        sb.append("</TABLE>\n</P>");

        DBAHelper.close(r);
        DBAHelper.close(s);

        sb.append(getHTMLFooter());

        return sb.toString();
    }

    private static void readSchemasUsed(Connection c) throws SQLException {
        Statement s = c.createStatement();
        ResultSet r = s.executeQuery("select distinct object_owner from sqlplan_detail where object_owner is not null order by object_owner");
        while (r.next()) {
            schemasUsed.add(r.getString(1));
        }
        DBAHelper.close(r);
        DBAHelper.close(s);
    }

    private static void readQueriesUsed(Connection c) throws SQLException {
        Statement s = c.createStatement();
        ResultSet r = s.executeQuery("select e.sql_id, e.child_number, count(*), t.command_type " +
                                     "from sqlexec e, sqltext t " +
                                     "where t.sql_id=e.sql_id " +
                                     "group by e.sql_id, e.child_number, t.command_type order by 3 desc");
        while (r.next()) {
            queriesUsed.add(new QueryUsed(r.getString(1), r.getInt(2), r.getLong(3), r.getInt(4)));
        }
        DBAHelper.close(r);
        DBAHelper.close(s);
    }

    private static void readObjectsUsed(Connection c) throws SQLException {
        Statement s = c.createStatement();
        ResultSet r = s.executeQuery("select distinct object_owner, object_name, object_type from sqlplan_detail where object_owner is not null order by object_owner, object_type, object_name");
        while (r.next()) {
            objectsUsed.add(new ObjectUsed(r.getString(1), r.getString(2), r.getString(3)));
        }
        DBAHelper.close(r);
        DBAHelper.close(s);
    }

    private static void printUsed(PrintWriter out) {
        out.println("<P>\n" +
                    "<H3><A NAME='99999'></A>Schemas, objects and queries used</H3>" +
                    "</P>");

        out.println("<P>\n" +
                    "Schemas" +
                    "</P>\n" +
                    "<P>\n" +
                    "<TABLE BORDER=1 WIDTH=1024>\n" +
                    "<TR><TH class='bg'>Schema</TH></TR>\n");

        int i = 0;
        for (String schema : schemasUsed) {
            out.println("<TR><TD ALIGN='right' class='" + (i % 2 == 0 ? "nc" : "c") + "'>" + schema + "</TD></TR>");
            i++;
        }

        out.println("</TABLE>\n</P>");

        out.println("<P>\n" +
                    "Objects" +
                    "</P>\n" +
                    "<P>\n" +
                    "<TABLE BORDER=1 WIDTH=1024>\n" +
                    "<TR><TH class='bg'>Owner</TH><TH class='bg'>Name</TH><TH class='bg'>Type</TH></TR>\n");

        i = 0;
        for (ObjectUsed o : objectsUsed) {
            out.print("<TR>");
            out.print("<TD ALIGN='right' class='" + (i % 2 == 0 ? "nc" : "c") + "'>" + o.owner + "</TD>");
            out.print("<TD ALIGN='right' class='" + (i % 2 == 0 ? "nc" : "c") + "'>" + o.name + "</TD>");
            out.print("<TD ALIGN='right' class='" + (i % 2 == 0 ? "nc" : "c") + "'>" + (o.type == null ? "&nbsp;" : o.type) + "</TD>");
            out.println("</TR>");
            i++;
        }

        out.println("</TABLE>\n</P>");

        out.println("<P>\n" +
                    "Queries" +
                    "</P>\n" +
                    "<P>\n" +
                    "<TABLE BORDER=1 WIDTH=1024>\n" +
                    "<TR><TH class='bg'>SQL Id</TH><TH class='bg'>Versions</TH><TH class='bg'>Type</TH><TH class='bg'>Executions</TH></TR>\n");

        i = 0;
        for (QueryUsed q : queriesUsed) {
            out.print("<TR>");
            out.print("<TD ALIGN='right' class='" + (i % 2 == 0 ? "nc" : "c") + "'><A HREF='#" + q.sqlId + "'>" + q.sqlId + "</A></TD>");
            out.print("<TD ALIGN='right' class='" + (i % 2 == 0 ? "nc" : "c") + "'>" + q.childNumber + "</TD>");
            out.print("<TD ALIGN='right' class='" + (i % 2 == 0 ? "nc" : "c") + "'>" + q.commandType + "</TD>");
            out.print("<TD ALIGN='right' class='" + (i % 2 == 0 ? "nc" : "c") + "'>" + q.executions + "</TD>");
            out.println("</TR>");
            i++;
        }

        out.println("</TABLE>\n</P>");
    }

    /**
     * Return a connection to the local report database.
     *
     * @return a connection to the local report database
     * @throws java.sql.SQLException in case of problem
     */
    static Connection getReportDbConnection() throws SQLException {
        final Connection c = DriverManager.getConnection("jdbc:h2:file:mydbadb/reportdb", "sa", "");
        c.setAutoCommit(false);
        return c;
    }

    /**
     * Format elapsed time.
     *
     * @param ms the duration in milli-second
     * @return the string representation of the duration
     */
    public static String formatTimeElapsed(long ms) {
        return formatTimeElapsed(ms, false);
    }

    /**
     * Format elapsed time.
     *
     * @param ms     the duration in milli-second
     * @param showMS display milli-seconds?
     * @return the string representation of the duration
     */
    public static String formatTimeElapsed(long ms, boolean showMS) {
        int s = (int) (ms / 1000);
        ms %= 1000;
        int m = (s / 60);
        s %= 60;
        int h = m / 60;
        m %= 60;

        return zero(h, 2) + ":" + zero(m, 2) + ":" + zero(s, 2) + (showMS ? "." + zero((int) ms, 3) : "");
    }

    /**
     * Pad a number with '0' and return the string representation of this number to the given limit.
     *
     * @param nb  the number for format
     * @param max the number of chars to get in return
     * @return the string representation of a number padded with '0' until a given limit (in size)
     */
    private static String zero(int nb, int max) {
        final StringBuilder ret = new StringBuilder(String.valueOf(nb));

        while (ret.length() < max) {
            ret.insert(0, '0');
        }

        return ret.toString();
    }

    static class ObjectUsed {
        public String owner;
        public String name;
        public String type;

        ObjectUsed(String owner, String name, String type) {
            this.owner = owner;
            this.name = name;
            this.type = type;
        }
    }

    static class QueryUsed {
        public String sqlId;
        public int childNumber;
        public long executions;
        public int commandType;

        QueryUsed(String sqlId, int childNumber, long executions, int commandType) {
            this.sqlId = sqlId;
            this.childNumber = childNumber;
            this.executions = executions;
            this.commandType = commandType;
        }
    }

    static class ExecutionStatistics {
        public String cg = "0";
        public String pr = "0";
        public String bstc = "0";
        public String brfc = "0";
        public String rttfc = "0";
        public String sortsmem = "0";
        public String sortsdisk = "0";
    }
}
